package org.zjx.db;


import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.TypeReference;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Modifier;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;


public class SQLiteHelper {
    private volatile static SQLiteHelper singleton;

    // 创建 SQLiteHelper 对象
    public static SQLiteHelper getSingleton() {
        if (singleton == null) {
            synchronized (SQLiteHelper.class) {
                if (singleton == null) {
                    singleton = new SQLiteHelper();
                }
            }
        }
        return singleton;
    }

    // 数据库连接字符串
    private static final String DB_URL = "jdbc:sqlite:mydatabase.db";
    private Connection conn;

    // 构造函数，打开数据库连接
    public SQLiteHelper() {
        try {
            // 创建数据库连接
            Class.forName("org.sqlite.JDBC"); // 加载 SQLite JDBC 驱动
            conn = DriverManager.getConnection(DB_URL);
            System.out.println("Database connected!");

        } catch (SQLException e) {
            System.out.println("Error while connecting to database: " + e.getMessage());
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    // 关闭数据库连接
    public void close() {
        try {
            if (conn != null && !conn.isClosed()) {
                conn.close();
                System.out.println("Database connection closed.");
            }
        } catch (SQLException e) {
            System.out.println("Error while closing connection: " + e.getMessage());
        }
    }

    public void createTable(String sql) {
        try (Statement stmt = conn.createStatement()) {
            stmt.execute(sql);
            System.out.println("table created successfully.");
        } catch (SQLException e) {
            System.out.println("Error creating table: " + e.getMessage());
        }
    }

    // 通用的插入/更新/删除数据的方法，接受SQL语句和参数
    public long executeUpdate(String sql, Object[] params) {
        long generatedId = -1; // 默认值，如果没有生成ID
        try (PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            // 设置 SQL 语句中的参数
            for (int i = 0; i < params.length; i++) {
                if (params[i] instanceof String) {
                    pstmt.setString(i + 1, (String) params[i]);
                } else if (params[i] instanceof Integer) {
                    pstmt.setInt(i + 1, (Integer) params[i]);
                } else if (params[i] instanceof Double) {
                    pstmt.setDouble(i + 1, (Double) params[i]);
                } else if (params[i] instanceof Long) {
                    pstmt.setLong(i + 1, (Long) params[i]);
                } else if (params[i] instanceof Boolean) {
                    pstmt.setBoolean(i + 1, (Boolean) params[i]);
                } else if (params[i] instanceof List) {
                    // 处理List类型参数，转换为JSON字符串存储
                    pstmt.setString(i + 1, JSON.toJSONString(params[i]));
                }
            }

            // 执行更新操作
            pstmt.executeUpdate();

            // 获取自增的主键ID
            try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    generatedId = generatedKeys.getLong(1); // 获取生成的第一个主键ID
                }
            }

        } catch (SQLException e) {
            System.out.println("Error executing SQL: " + e.getMessage());
        }

        return generatedId; // 返回生成的ID
    }

    // 通用的查询方法，接受 SQL 语句和参数，返回查询结果的 List
    @SuppressWarnings("unchecked")
    public <T> List<T> executeQuery(String sql, Object[] params, Class<T> clazz) {
        List<T> resultList = new ArrayList<>();
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            // 设置 SQL 语句中的参数
            if (params == null) params = new Object[0];
            for (int i = 0; i < params.length; i++) {
                if (params[i] instanceof String) {
                    pstmt.setString(i + 1, (String) params[i]);
                } else if (params[i] instanceof Integer) {
                    pstmt.setInt(i + 1, (Integer) params[i]);
                } else if (params[i] instanceof Double) {
                    pstmt.setDouble(i + 1, (Double) params[i]);
                } else if (params[i] instanceof Long) {
                    pstmt.setLong(i + 1, (Long) params[i]);
                } else if (params[i] instanceof Boolean) {
                    pstmt.setBoolean(i + 1, (Boolean) params[i]);
                }
            }

            // 执行查询
            try (ResultSet rs = pstmt.executeQuery()) {
                if (sql.trim().toUpperCase().startsWith("SELECT COUNT")) {
                    // 如果是 COUNT(*) 查询，直接获取单一的整数结果
                    if (rs.next()) {
                        int count = rs.getInt(1); // 获取 COUNT(*) 结果
                        resultList.add((T) Integer.valueOf(count)); // 结果转为 Integer 并添加到 resultList
                    }
                } else {
                    // 普通查询，进行转换
                    while (rs.next()) {
                        T obj = resultSetToBean(rs, clazz); // 自动转换
                        resultList.add(obj);
                    }
                }
            } catch (NoSuchFieldException e) {
                throw new RuntimeException(e);
            }
        } catch (SQLException | NoSuchMethodException | InvocationTargetException | InstantiationException |
                 IllegalAccessException e) {
            System.out.println("Error executing query: " + e.getMessage());
        }
        return resultList;
    }

    // 在SQLiteHelper类中添加这个改进方法
    public <T> T resultSetToBean(ResultSet rs, Class<T> clazz)
            throws SQLException, NoSuchMethodException, InvocationTargetException,
            InstantiationException, IllegalAccessException, NoSuchFieldException {

        T obj = clazz.getDeclaredConstructor().newInstance();

        for (Field field : clazz.getDeclaredFields()) {
            if (Modifier.isStatic(field.getModifiers())) continue;

            field.setAccessible(true);
            String columnName = field.getName();

            // 处理枚举类型
            if (field.getType().isEnum()) {
                String enumValue = rs.getString(columnName);
                if (enumValue != null) {
                    Object[] enumConstants = field.getType().getEnumConstants();
                    for (Object enumConstant : enumConstants) {
                        if (enumConstant.toString().equals(enumValue)) {
                            field.set(obj, enumConstant);
                            break;
                        }
                    }
                }
                continue;
            }

            // 处理Map类型字段
            if (Map.class.isAssignableFrom(field.getType())) {
                String jsonValue = rs.getString(columnName);
                if (jsonValue != null && !jsonValue.isEmpty() && !"{}".equals(jsonValue)) {
                    try {
                        Map<String, Object> map = JSON.parseObject(jsonValue,
                                new TypeReference<Map<String, Object>>() {}.getType());
                        field.set(obj, map);
                    } catch (Exception e) {
                        System.err.println("FastJson2 parse error for field " + columnName + ": " + jsonValue);
                        field.set(obj, Collections.emptyMap());
                    }
                } else {
                    field.set(obj, Collections.emptyMap());
                }
                continue;
            }

            // 处理List<String>类型字段
            if (List.class.isAssignableFrom(field.getType()) && field.getGenericType().getTypeName().contains("String")) {
                String jsonValue = rs.getString(columnName);
                if (jsonValue != null && !jsonValue.isEmpty() && !"[]".equals(jsonValue)) {
                    try {
                        List<String> list = JSON.parseObject(jsonValue,
                                new TypeReference<List<String>>() {}.getType());
                        field.set(obj, list);
                    } catch (Exception e) {
                        System.err.println("FastJson2 parse error for field " + columnName + ": " + jsonValue);
                        field.set(obj, Collections.emptyList());
                    }
                } else {
                    field.set(obj, Collections.emptyList());
                }
                continue;
            }

            // 基本类型处理
            Class<?> fieldType = field.getType();
            if (fieldType == String.class) {
                field.set(obj, rs.getString(columnName));
            } else if (fieldType == int.class || fieldType == Integer.class) {
                field.set(obj, rs.getInt(columnName));
            } else if (fieldType == long.class || fieldType == Long.class) {
                field.set(obj, rs.getLong(columnName));
            } else if (fieldType == double.class || fieldType == Double.class) {
                field.set(obj, rs.getDouble(columnName));
            } else if (fieldType == boolean.class || fieldType == Boolean.class) {
                field.set(obj, rs.getBoolean(columnName));
            }
        }
        return obj;
    }

    // 清空数据
    public void clearData(String tableName) {
        String sql = "DELETE FROM " + tableName;

        try (Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(sql);
            System.out.println("Data cleared from " + tableName);
        } catch (SQLException e) {
            System.out.println("Error clearing data: " + e.getMessage());
        }
    }

    // 清空表（删除表）
    public void dropTable(String tableName) {
        String sql = "DROP TABLE IF EXISTS " + tableName;

        try (Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(sql);
            System.out.println("Table " + tableName + " dropped.");
        } catch (SQLException e) {
            System.out.println("Error dropping table: " + e.getMessage());
        }
    }
}